package booroo.service;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import booroo.interfaces.SungJukService;
import booroo.model.SungJuk_V0;

public class JDBCSungJuk implements SungJukService{

	// JDBC접속정보
	private static final String DRV = "oracle.jdbc.OracleDriver";
	private static final String URL = "jdbc:oracle:thin:@//localhost:1521/xe";
	private static final String USR = "scott";
	private static final String PWD = "TIGER";
	
	// SQL문 작성
	private static final String insertSQL = "insert into sungjuk(sjno,name,kor,eng,mat)	values(sjno.nextval,?,?,?,?)";
	private static final String selectSQL = "select * from sungjuk order by sjno desc";
	private static final String deleteSQL = "delete from sungjuk where sjno = ?";
	private static final String updateSQL = "update sungjuk set name=?,kor=?,eng=?,mat=? where sjno = ?";
	
	@Override
	public boolean newSungJuk(SungJuk_V0 sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(insertSQL);
			// ? 매개변수에 들어갈 실제값을 지정
			ps.setString(1, sj.getName());
			ps.setInt(2, sj.getKor());
			ps.setInt(3, sj.getEng());
			ps.setInt(4, sj.getMat());
			
			int cnt = ps.executeUpdate();	// 성공적으로 insert되면 result를 true로
			if(cnt > 0) result = true;
		} catch(Exception ex){
			ex.printStackTrace();
		} finally{
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		
		
		return result;
	}

	@Override
	public String readSungJuk() {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String result = "";
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(selectSQL);
			rs = ps.executeQuery();
			// selectSQL실행결과를 모두 출력
			while(rs.next()){
				for(int i = 1; i<=9; ++i)
					result += rs.getString(i) + " ";
					result += "\n";
			}
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(rs != null)
				try{rs.close();}catch(Exception e){}
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
		}
		return result;
	}

	@Override
	public boolean modifySungJuk(SungJuk_V0 sj) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(updateSQL);
			ps.setString(1,sj.getName());
			ps.setInt(2,sj.getKor());
			ps.setInt(3,sj.getEng());
			ps.setInt(4,sj.getMat());
			ps.setString(5,sj.getSjno());
			
			int cnt = ps.executeUpdate();
			if(cnt > 0) result = true;
					
		}catch(Exception ex){ex.printStackTrace();}
		finally{
			if(ps != null)try{ps.close();}catch(Exception e){}
			if(conn != null)try{conn.close();}catch(Exception e){}
		}
		return result;
	}

	@Override
	public boolean removeSungJuk(String key) {
		Connection conn = null;
		PreparedStatement ps = null;
		boolean result = false;
		
		try{
			Class.forName(DRV);
			conn = DriverManager.getConnection(URL,USR,PWD);
			ps = conn.prepareStatement(deleteSQL);
			ps.setString(1,key);
			
			int cnt = ps.executeUpdate();
			if(cnt > 0) result = true;
										
		}catch(Exception ex){
			ex.printStackTrace();
		}finally{
			if(ps != null)
				try{ps.close();}catch(Exception e){}
			if(conn != null)
				try{conn.close();}catch(Exception e){}
			
		}
		return result;
		
	}

		
}
